with tmp_tab as (
    select 
        scansitecode
        ,nvl(sum(door),0) as door_sign  -- 送货上门
        ,nvl(sum(if(t3.waybill_no is not null,1,0)),0) as xusign  -- 虚假签收
        ,nvl(sum(yizhan),0) as stage_sign -- 送入驿站
    from (
        select billcode,scansitecode,scantime,if(str <> '驿站',1,0) as door
              ,if(str = '驿站',1,0) as yizhan,row_number() over(partition by billcode order by scantime asc) as row_id
        from (
            select   billcode,scansitecode,delivertime as scantime,case
                       when receipttype like '%门口%' then '门口'
                       when receipttype like '%前台%' then '前台'
                       when receipttype like '%水表箱%' then '水表箱'
                       when receipttype like '%电表箱%' then '电表箱'
                       when receipttype like '%家门签收%' then '家门签收'
                       when receipttype like '%家人%' then '家人'
                       when receipttype like '%朋友%' then '朋友'
                       when receipttype like '%同事代收%' then '同事代收'
                       when receipttype like '%本人签收%' then '本人签收'
                       when receipttype like '%门把手%' then '门把手'
                       when receipttype like '%亲戚%' then '亲戚'
                       when receipttype like '%亲属%' then '亲属'
                       when receipttype like '%同学%' then '同学'
                       when receipttype like '%驿站%' then '驿站'
                   else null end str
            from jms_ods.tab_barscan_sign
            where dt >= '{{ execution_date | date_add(-6) | cst_ds }}' and dt <= '{{ execution_date | cst_ds }}'
            and (receipttype like '%门口%'
                        or receipttype like '%前台%'
                        or receipttype like '%水表箱%'
                        or receipttype like '%电表箱%'
                        or receipttype like '%家门签收%'
                        or receipttype like '%家人%'
                        or receipttype like '%朋友%'
                        or receipttype like '%同事代收%'
                        or receipttype like '%本人签收%'
                        or receipttype like '%门把手%'
                        or receipttype like '%亲戚%'
                        or receipttype like '%亲属%'
                        or receipttype like '%同学%'
                        or receipttype like '%驿站%')
        ) t1
    ) t2
    left join (
        select waybill_no
        from jms_dwd.dwd_sqs_objection_info_base_dt --虚假签收
        where
        --dt between date_add('2022-10-16', -45) and '2022-10-16'
        dt between '{{ execution_date | date_add(-45) | cst_ds }}'  and '{{ execution_date | cst_ds }}'
        and first_type_code = '03'
        and second_type_code = '0302'
        and source_type = 1
        group by waybill_no
    ) t3 
    on t2.billcode = t3.waybill_no
    where row_id = 1 and substr(scantime,1,10) = '{{ execution_date | cst_ds }}'
    group by scansitecode
)

,tmp_tab_sging as (
       
       select
        count(distinct billcode ) as aging_sign ,
        tt.agent_code as end_agent_code,
        tt.agent_name as end_agent_name,
        tt.name as final_sign_network_name,
        scansitecode as final_sign_network_code,
        dt,
        sum(case when type=1 then 1 else 0 end )  stage_sign11
    from
        (
--         select
--             billcode,
--             scansitecode,
--             scantime ,
--             to_date(scantime) as dt,
--             1 as type
--
--         from
--             jms_ods.tab_barscan_collect
--         where
--             dt >= '2022-10-09'
--             and dt <= '2022-10-16'
--             and scantype = '入库扫描'
        select
            waybill_no as billcode  ,
            scan_site_code as scansitecode,
            scan_time as scantime ,
            to_date(scan_time) as dt,
            1 as type

        from
            jms_dwd.dwd_tab_barscan_collect_base_dt
        where
            dt >= '{{ execution_date | date_add(-6) | cst_ds }}'
            and dt <= '{{ execution_date | cst_ds }}'
            and scan_type = '入库扫描'
    union all
        select
            waybill_no as billcode,
            signed_site_code as scansitecode,
            signed_time  as scantime,
            to_date(signed_time) as dt,
            2 as type
        from
            jms_dwd.dwd_tab_barscan_sign_base_dt
        where
            dt >= '{{ execution_date | date_add(-6) | cst_ds }}'
            and dt <= '{{ execution_date | cst_ds }}'
--         select
--             billcode,
--             scansitecode,
--             delivertime as scantime,
--             to_date(delivertime) as dt,
--             2 as type
--         from
--             jms_ods.tab_barscan_sign
--         where
--             dt >= '2022-10-09'
--             and dt <= '2022-10-16'
         ) t
    left join jms_dim.dim_network_whole_massage tt on
        tt.code = t.scansitecode
    where
        dt = '{{ execution_date | cst_ds }}'
    group by 
                tt.agent_code,
        tt.agent_name,
        tt.name ,
        scansitecode,
        dt
)
insert overwrite table jms_dm.dm_door_sign_rate_dt partition (dt)
select 
     dt as date_time
    ,end_agent_code
    ,end_agent_name
    ,final_sign_network_code
    ,final_sign_network_name
    ,nvl(aging_sign,0) as aging_sign
    ,nvl(door_sign,0) as door_sign
    ,nvl(xusign,0) as xusign
    ,nvl((door_sign - xusign) / aging_sign ,0) as door_sign_rate
    ,nvl(stage_sign11,0) as stage_sign
    ,null as expand1
    ,null as expand2
    ,null as expand3
    ,null as expand4
    ,null as expand5
    ,dt
from tmp_tab_sging t1
left join tmp_tab t2
on t1.final_sign_network_code = t2.scansitecode
 
 distribute by pmod(hash(rand()), 3);
